package videowarehouseapp.dataobjects;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;

import videowarehouseapp.ApplicationException;
import videowarehouseapp.Constants;
import videowarehouseapp.Stock;

public class StockDO implements Stock{
	
	Connection conn = null; 
	PreparedStatement 
		psNewItem = null, 
		psUpdItem = null, psUpdBought = null, psUpdSold = null,
		psDelItem = null,
		psGetItem = null, psGetItemWhse = null, psGetHoldWhse = null;
	
	public StockDO(){
		try {
			conn = Constants.getConnection();
			
			psNewItem = conn.prepareStatement(
					"INSERT INTO Stock (catalogue_id, warehouse, holding) VALUES (?, ?, ?)",
					Statement.RETURN_GENERATED_KEYS);
			
			psUpdItem = conn.prepareStatement(
					"UPDATE Stock SET catalogue_id = ?, warehouse = ?, holding = ? WHERE ID = ?");
			
			psUpdBought = conn.prepareStatement(
					"UPDATE Stock SET holding = holding + ? WHERE catalogue_id = ? AND warehouse = ?");
			
			psUpdSold = conn.prepareStatement(
					"UPDATE Stock SET holding = holding - ? WHERE catalogue_id = ? AND warehouse = ?");
			
			psDelItem = conn.prepareStatement(
					"DELETE FROM Stock WHERE ID = ?");
						
			psGetItem = conn.prepareStatement(
					"SELECT id, catalogue_id, warehouse, holding FROM Stock WHERE ID = ?");
			
			psGetItemWhse = conn.prepareStatement(
					"SELECT id, catalogue_id, warehouse, holding FROM Stock WHERE warehouse = ?"); 
			
			psGetHoldWhse = conn.prepareStatement(
					"SELECT holding FROM Stock WHERE warehouse = ? AND catalogue_id = ?");
			
		} catch (SQLException e) {
			Constants.SQLExceptionHandler(e);
		}
	}
	
	public int createItem(StockInfo stock) {
		int itemId  = -1;
		if(stock != null){
			try{
				// (catalogue_id, warehouse, holding)
				psNewItem.setInt(1, stock.catalogue_id);
				psNewItem.setString(2, stock.warehouse);
				psNewItem.setInt(3, stock.holding);
				
				if(psNewItem.executeUpdate() >0){
					ResultSet rs = psNewItem.getGeneratedKeys();
					if(rs.next()){
						itemId = rs.getInt(1);
					}		
					rs.close();
				}

			}catch(SQLException sqe){
				Constants.SQLExceptionHandler(sqe);
			}
			
		}		
		return itemId;
	}

	public void deleteItem(int id) {
		try{
			psDelItem.setInt(1, id);
			psDelItem.executeUpdate();
			
		}catch(SQLException sqe){
			Constants.SQLExceptionHandler(sqe);
		}
	}


	public int getHoldingByWarehouse(int catalogue_id, String warehouse) {
		int holding = 0;
		try{
			psGetHoldWhse.setString(1, warehouse);
			psGetHoldWhse.setInt(2, catalogue_id);
			ResultSet rs = psGetHoldWhse.executeQuery();
			if(rs.next()){
				holding = rs.getInt("holding");				
			}
			rs.close();
		}catch(SQLException sqe){
			Constants.SQLExceptionHandler(sqe);
		}
		return holding;
	}

	public StockInfo getItemById(int id) {
		StockInfo sif = null;
		try{
			psGetItem.setInt(1, id);
			ResultSet rs = psGetItem.executeQuery();
			if(rs.next()){
				// id, catalogue_id, warehouse, holding
				sif = new StockInfo(
						id,
					rs.getInt("catalogue_id"),
					rs.getString("warehouse"),
					rs.getInt("holding")
				);
				
			}
			rs.close();
		}catch(SQLException sqe){
			Constants.SQLExceptionHandler(sqe);
		}
		return sif;
	}

	public List<StockInfo> getItemByWarehouse(String warehouse) {
		LinkedList<StockInfo> sif = new LinkedList<StockInfo>();
		
		try{
			psGetItemWhse.setString(1, warehouse);
			ResultSet rs = psGetItemWhse.executeQuery();
			while(rs.next()){
				// id, catalogue_id, warehouse, holding
				sif.add( new StockInfo(
					rs.getInt("id"),
					rs.getInt("catalogue_id"),
					rs.getString("warehouse"),
					rs.getInt("holding")
				)
				);
				
			}
			rs.close();
		}catch(SQLException sqe){
			Constants.SQLExceptionHandler(sqe);
		}
		return sif;
	}

	public void modifyItem(StockInfo stock) {
		if(stock != null){
			
			try{
				psUpdItem.setInt(1, stock.catalogue_id);
				psUpdItem.setString(2, stock.warehouse);
				psUpdItem.setInt(3, stock.holding);
				psUpdItem.setInt(4, stock.id);
				psUpdItem.executeUpdate();
				
			}catch(SQLException sqe){
				Constants.SQLExceptionHandler(sqe);
			}
		}
		
	}

	public int bought(int catalogue_id, int quantity, String warehouse) {
		int holding =  0;
		
		try{
			psUpdBought.setInt(1, quantity);
			psUpdBought.setInt(2, catalogue_id);
			psUpdBought.setString(3, warehouse);
			int res =  psUpdBought.executeUpdate();
			if(res > 0){
				//get new holding
				holding = getHoldingByWarehouse(catalogue_id, warehouse);
			}else{
				//update failed because stock item does not exist
				StockInfo sif = new StockInfo(0, catalogue_id, warehouse, quantity);
				if(createItem(sif) > 0){
					holding = quantity;
				}
			}
		}catch(SQLException sqe){
			Constants.SQLExceptionHandler(sqe);
		}
		
		return holding;
	}

	public int sold(int catalogue_id, int quantity, String warehouse) throws ApplicationException {
		int holding = getHoldingByWarehouse(catalogue_id, warehouse);
		
		if(holding < quantity){
			throw new ApplicationException("Attempt to sell more goods than held in stock");
		}
		
		try {
			psUpdSold.setInt(1, quantity);
			psUpdSold.setInt(2, catalogue_id);
			psUpdSold.setString(3, warehouse);
			psUpdSold.executeUpdate();
			
		} catch (SQLException sqe) {
			Constants.SQLExceptionHandler(sqe);
		}
		
		return holding- quantity;
	}

}
